package top.leyi.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import top.leyi.entity.SQLUtil;
import top.leyi.entity.Talk.SimpleComment;
import top.leyi.entity.Talk.SimpleTalk;
import top.leyi.entity.Talk.Talk;

import java.util.List;

public interface TalkMapper {

    @Select("SELECT t.id, content,t.time, username,u.id as user_id ,u.headPic, pics " +
            "FROM talk t  " +
            "join user u on t.user_id = u.id " +
            "where t.is_delete = 0  " +
            "ORDER by t.id DESC  "  +
            "LIMIT #{limit} OFFSET #{offset}")
    List<SimpleTalk> queryTalks(SQLUtil sqlUtil);

    @Select("SELECT t.id, content,t.time, username,u.id as user_id ,u.headPic, pics " +
            "FROM talk t  " +
            "join user u on t.user_id = u.id " +
            "where t.id = #{value}")
    SimpleTalk getTalkWithId(int talkId);


    @Select("SELECT t.id, content,t.time, username,u.id as user_id ,u.headPic, pics " +
            "FROM talk t  " +
            "join user u on t.user_id = u.id " +
            "where t.is_delete = 0  and content like \"%${key}%\" " +
            "ORDER by t.id DESC  "  +
            "LIMIT #{limit} OFFSET #{offset}")
    List<SimpleTalk> fuzzyQueryTalks(SQLUtil sqlUtil);

    /**
     * 查询个人历史发过的说说
     * @param sqlUtil
     * @return
     */

    @Select("SELECT t.id, content,t.time, username,u.id as user_id ,u.headPic, pics " +
            "FROM talk t  " +
            "join user u on t.user_id = u.id " +
            "where t.is_delete = 0  and content like \"%${key}%\" " +
            "and u.id = #{id} "  +
            "ORDER by t.id DESC  "  +
            "LIMIT #{limit} OFFSET #{offset}")
    List<SimpleTalk> fuzzyQueryHisTalks(SQLUtil sqlUtil);


    /**
     * @desc 获取最热门说说
     * @return
     */
    @Select("SELECT  t.id, t.content, t.time, username,u.id as user_id ,u.headPic, t.pics,\n" +
            "(select count(*) from  talk_comment tc where talk_id = t.id and tc.is_delete = 0  ) as comments,\n" +
            "(\n" +
            " select count(*) from good g where g.talkId = t.id and g.state = 1\n" +
            ") as likes,\n" +
            "(\n" +
            "select  t.id * (likes + comments) as score  \n" +
            ") \n" +
            "as score\n" +
            "FROM talk t\n" +
            "join user u \n" +
            "on t.user_id = u.id\n" +
            "where t.is_delete = 0 \n" +
            "ORDER by score  \n" +
            "DESC LIMIT 20 OFFSET 0")
    List<SimpleTalk> queryHotTalks();


    @Select("select userId from good where talkId = #{value} and state = 1")
    List<Integer> queryVoteUser(int talkId);

    @Insert("INSERT INTO talk(content, user_id, pics) values(#{content}, #{user_id}, #{pics})")
    void  addTalk(Talk talk);

    @Select("select * from talk where user_id = #{value} ORDER BY id DESC LIMIT 1 ")
    Talk getRencentlyTalk(int userId);

    @Insert("INSERT INTO talk(content, user_id, pics, is_delete) values (#{content}, #{user_id}, #{pics}, 1)")
    void addDeathTalk(Talk talk);

    @Insert("INSERT INTO talk_comment(content, user_id, pics, talk_id, is_delete)" +
            " VALUES (#{content}, #{user_id}, #{pics}, #{id}, 0) ")
    void  addComment(Talk talk);

    @Select("SELECT t.*, content, username,u.id as user_id, pics   FROM talk t join user u on t.user_id = u.id  " +
            " WHERE t.id = #{value} ")
    Talk queryOneTalk(String id);

    @Select("SELECT  u.id    FROM talk t join user u on t.user_id = u.id  " +
            " WHERE t.id = #{value} ")
    int queryTalkUserId(int id);

    @Select("select * from talk where id = #{value} LIMIT 1 ")
    SimpleTalk queryDeathTalk(int id);

    @Select("select * from talk where user_id = #{value} ORDER BY id DESC LIMIT 1 ")
    Talk queryDeathTalkWithUserId(int userId);

    @Select("SELECT t.*, content, username, pics   FROM talk t join user u on t.user_id = u.id  " +
            " WHERE t.id = #{value} ")
    SimpleTalk querySimpleTalk(int id);

    @Select("SELECT content, username, pics,tc.id,tc.user_id  " +
            "FROM talk_comment tc JOIN user u on u.id = tc.user_id  " +
            "WHERE tc.talk_id = #{value} and tc.is_delete = 0")
    List<SimpleComment> queryComments (String id);

    @Select("SELECT COUNT(*) FROM good where talkId = #{value} and state = 1")
    int queryLikes(int id);

    @Select("SELECT state FROM good WHERE talkId = #{id} AND userId = #{user_id}")
    String queryGoodState(Talk good);

    @Update("UPDATE good SET state = 1  WHERE  talkId = #{id} AND userId = #{user_id}")
    void setGoodTrue(Talk good);

    @Update("UPDATE good SET state = 0  WHERE  talkId = #{id} AND userId = #{user_id}")
    void setGoodFalse(Talk good);

    @Insert("INSERT INTO good(talkId,userId,state) VALUES(#{id}, #{user_id}, 1)")
    void addGood(Talk good);

    /**
     * @desc 删除说说
     */
    @Update("UPDATE talk set is_delete = 1 WHERE id = #{value}")
    void deleteOneTalk(int talkId);

    /**
     * @desc 删除评论
     */
    @Update("UPDATE talk_comment set is_delete = 1 WHERE id = #{value}")
    void deleteOneTalkComment(int CommentId);

    /**
     * @desc 查询评论的talkId
     */
    @Select("select talk_id from talk_comment where id = #{value}")
    int queryCommenTalkId(int commentId);
}
